package com.zhao.fishingguideserver.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 数据看板统计数据访问接口
 */
@Mapper
public interface DashboardMapper {
    
    /**
     * 统计用户总数
     */
    @Select("SELECT COUNT(*) FROM users")
    int countTotalUsers();
    
    /**
     * 统计今日新增用户数
     */
    @Select("SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE()")
    int countTodayNewUsers();
    
    /**
     * 统计商品总数
     */
    @Select("SELECT COUNT(*) FROM product")
    int countTotalProducts();
    
    /**
     * 统计在售商品数
     */
    @Select("SELECT COUNT(*) FROM product WHERE status = 'available'")
    int countAvailableProducts();
    
    /**
     * 统计订单总数
     */
    @Select("SELECT COUNT(*) FROM orders")
    int countTotalOrders();
    
    /**
     * 统计今日订单数
     */
    @Select("SELECT COUNT(*) FROM orders WHERE DATE(create_time) = CURDATE()")
    int countTodayOrders();
    
    /**
     * 统计交易总金额
     */
    @Select("SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE status IN ('paid', 'shipped', 'delivered')")
    Double countTotalAmount();
    
    /**
     * 统计今日交易金额
     */
    @Select("SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE DATE(create_time) = CURDATE() AND status IN ('paid', 'shipped', 'delivered')")
    Double countTodayAmount();
    
    /**
     * 统计帖子总数
     */
    @Select("SELECT COUNT(*) FROM posts WHERE status = 'published'")
    int countTotalPosts();
    
    /**
     * 统计今日新增帖子数
     */
    @Select("SELECT COUNT(*) FROM posts WHERE DATE(create_time) = CURDATE() AND status = 'published'")
    int countTodayNewPosts();
    
    /**
     * 统计评论总数
     */
    @Select("SELECT COUNT(*) FROM comments WHERE status = 'published'")
    int countTotalComments();
    
    /**
     * 统计点赞总数
     */
    @Select("SELECT COUNT(*) FROM post_likes")
    int countTotalLikes();
    
    /**
     * 查询用户增长趋势（最近30天）
     */
    @Select("SELECT DATE(created_at) as date, COUNT(*) as count " +
            "FROM users " +
            "WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY DATE(created_at) " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectUserGrowthTrend();
    
    /**
     * 查询商品发布趋势（最近30天）
     */
    @Select("SELECT DATE(creat_time) as date, COUNT(*) as count " +
            "FROM product " +
            "WHERE creat_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY DATE(creat_time) " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectProductTrend();
    
    /**
     * 查询销售额趋势（最近30天）
     */
    @Select("SELECT DATE(create_time) as date, COALESCE(SUM(total_amount), 0) as amount " +
            "FROM orders " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "AND status IN ('paid', 'shipped', 'delivered') " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectSalesTrend();
    
    /**
     * 查询帖子发布趋势（最近30天）
     */
    @Select("SELECT DATE(create_time) as date, COUNT(*) as count " +
            "FROM posts " +
            "WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "AND status = 'published' " +
            "GROUP BY DATE(create_time) " +
            "ORDER BY date ASC")
    List<Map<String, Object>> selectPostTrend();
    
    /**
     * 查询热门帖子Top10
     */
    @Select("SELECT p.id, p.title, p.view_count as viewCount, p.like_count as likeCount, " +
            "p.comment_count as commentCount, u.nick_name as author, p.create_time as createTime " +
            "FROM posts p " +
            "LEFT JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.status = 'published' " +
            "ORDER BY (p.like_count * 2 + p.comment_count + p.view_count * 0.1) DESC " +
            "LIMIT 10")
    List<Map<String, Object>> selectHotPosts();
    
    /**
     * 查询活跃用户Top10
     */
    @Select("SELECT u.user_id as userId, u.username, u.nick_name as nickName, " +
            "(SELECT COUNT(*) FROM posts WHERE user_id = u.user_id AND status = 'published') as postCount, " +
            "(SELECT COUNT(*) FROM comments WHERE user_id = u.user_id AND status = 'published') as commentCount, " +
            "(SELECT COUNT(*) FROM product WHERE user_id = u.user_id) as productCount " +
            "FROM users u " +
            "ORDER BY (postCount * 3 + commentCount + productCount * 2) DESC " +
            "LIMIT 10")
    List<Map<String, Object>> selectActiveUsers();
    
    /**
     * 各状态商品统计
     */
    @Select("SELECT status, COUNT(*) as count FROM product GROUP BY status")
    List<Map<String, Object>> countProductsByStatus();
    
    /**
     * 各状态订单统计
     */
    @Select("SELECT status, COUNT(*) as count FROM orders GROUP BY status")
    List<Map<String, Object>> countOrdersByStatus();
}

